﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;

/// <summary>
/// CSClass 的摘要说明
/// </summary>
public class CSClass
{
    public string djbh { get; set; }
    public string rq { get; set; }
    public string month { get; set; }
    public string[] LaborageSeniority;
    public string[] Laborage;
    public string[] LaborageAssess;
    public string[] LaborageAF;

    public CSClass()
    {
        //
        // TODO: 在此处添加构造函数逻辑
        //
    }

    DataTable dt;
    MsSqlHelper shelper = new MsSqlHelper();
    MsSqlDao msdao = new MsSqlDao();
    /// <summary>
    /// 执行SQL返货执行信息
    /// </summary>
    /// <param name="SqlCommand"></param>
    /// <param name="MSG"></param>
    /// <returns></returns>
    public string CalculateExecute(string SqlCommand, string MSG)
    {
        try
        {
            string message;
            //SqlCommand = "select rqtime as [value],rtrim(rqtime) as [key] from [Web_Laborage] where is_zx='否'";
            shelper.ExecuteSql(SqlCommand);
            if (SqlCommand.IndexOf("delete") >= 0)
            {
                message = "删除[" + MSG + "]数据成功\r\n";
            }
            else
                if (SqlCommand.IndexOf("insert into") >= 0)
                {
                    message = "添加[" + MSG + "]数据成功\r\n";
                }
                else
                    if (SqlCommand.IndexOf("update") >= 0)
                    {
                        message = "更新[" + MSG + "]数据成功\r\n";
                    }
                    else
                    {
                        message = "未知操作：[" + MSG + "]\r\n";
                    }
            return message;
        }
        catch (Exception ex)
        {
            WriteLog(ex.ToString(), true);
            return ex.Message;
        }
    }
    /// <summary>
    /// 获取工资基础数据
    /// </summary>
    public void PubMAX()
    {
        dt = shelper.ExecuteDt(" ");
        SqlDataReader dr1 = shelper.ExecuteReader("select replace(max(djbh),'','') maxdjbh,replace(max(rq),'','') maxrq,replace(max(rqtime),'','') maxrqtime from [Web_LaborageDetailed] where rqtime not like '%00%'");
        if (dr1.Read())
        {
            djbh = dr1["maxdjbh"].ToString();
            rq = dr1["maxrq"].ToString();
            month = dr1["maxrqtime"].ToString();
        }

        SqlDataReader dr2 = shelper.ExecuteReader("Select SetLaborageSettings,SetLaborageSeniority,SetLaborageAssess,SetLaborageAccumulationFund from [Web_SiteSettings]");
        if (dr2.Read())
        {
            Laborage = dr2["SetLaborageSettings"].ToString().Split('|');
            LaborageSeniority = dr2["SetLaborageSeniority"].ToString().Split('|');
            LaborageAssess = dr2["SetLaborageAssess"].ToString().Split('|');
            LaborageAF = dr2["SetLaborageAccumulationFund"].ToString().Split('|');
        }
    }
    /// <summary>
    /// 重算工资表
    /// </summary>
    /// <returns></returns>
    public string Redone()
    {

        try
        {
            string returntext = "";
            string Sql;
            PubMAX();
            //returntext =returntext + "<td bgcolor=\"#FFFFFF\"><br>";
            //
            returntext = returntext + "<font color=red>正在删除不需要的相关数据...</font><br><br>";
            returntext = returntext + "<textarea cols=100 rows=6>";
            Sql = "delete from [Web_LaborageDetailed] where ygbh in (select ygbh from [Web_Userinfo] where beactive='否' Or State='离职' Or State='停薪留职') and is_zx='否' and djbh=(select max(djbh) from [Web_LaborageDetailed] where is_zx='否')";
            returntext = returntext + CalculateExecute(Sql, "【本月已离职人员工资】数据");

            Sql = "delete from [Web_LaborageDetailed] where ygbh in (select ygbh from [Web_Userinfo] where beactive='否' Or State='离职' Or State='停薪留职') and is_zx='否' and djbh=(select max(djbh) from [Web_LaborageDetailed] where is_zx='否')";
            returntext = returntext + CalculateExecute(Sql, "【本月已离职人员考勤】数据");

            Sql = "delete from [Web_SocialSecurity] where ygbh in (select ygbh from [Web_Userinfo] where beactive='否' Or State='离职' Or State='停薪留职') and is_zx='否' and djbh=(select max(djbh) from [Web_LaborageDetailed] where is_zx='否')";
            returntext = returntext + CalculateExecute(Sql, "【本月已离职人员保险】数据");

            Sql = "delete from [Web_LaborageOther] where ygbh in (select ygbh from [Web_Userinfo] where beactive='否' Or State='离职' Or State='停薪留职') and is_zx='否' and djbh=(select max(djbh) from [Web_LaborageDetailed] where is_zx='否')";
            returntext = returntext + CalculateExecute(Sql, "【本月已离职人员其他工资】数据");

            Sql = "delete from [Web_OverTime] where ygbh in (select ygbh from [Web_Userinfo] where beactive='否' Or State='离职' Or State='停薪留职') and is_zx='否' and djbh=(select max(djbh) from [Web_LaborageDetailed] where is_zx='否')";
            returntext = returntext + CalculateExecute(Sql, "【本月已离职人员加班工资】数据");
                      
            //正在添加工资数据
            returntext = returntext + "</textarea><br /><br /><font color=red>正在添加工资数据...</font><br /><br />";
            returntext = returntext + "<textarea cols=100 rows=7>";

            Sql = "insert into [Web_SalaryPerformance](rqtime,bmid,jxf,pay,SalaryPay,hzjxf,pjjxf,SalaryMode,is_zx,djbh) "
                + " select '" + month + "' rqtime,Did bmid,0 jxf,0 pay,0 SalaryPay,0 hzjxf,0 pjjxf,0 SalaryMode,'否' si_zx,'" + djbh + "' djbh "
                + " from [Web_Department] where beactive='是' and Sid<>0 and Did not in(select bmid from [Web_SalaryPerformance] where is_zx='否' and djbh in(select max(djbh) djbh from [Web_LaborageDetailed])) ";
            returntext = returntext + CalculateExecute(Sql, "【部门绩效】基础数据");

            Sql = "insert into [Web_LaborageOther] (djbh,ygbh,rqtime,is_zx) select djbh,ygbh,rqtime,'否' as is_zx "
                +" from [Web_LaborageDetailed] where ygbh not in(select ygbh from [Web_LaborageOther] where djbh in(select max(djbh) djbh from [Web_LaborageDetailed])) and djbh in(select max(djbh) djbh from [Web_LaborageDetailed] where is_zx='否')";
            returntext = returntext + CalculateExecute(Sql, "【其他代扣】基础数据");

            Sql = "insert into [Web_OverTime] select djbh, ygbh, case when jbgz<1000 then cast(1200.00/30 as decimal(38,2)) else cast(jbgz/30 as decimal(38,2)) end as jbgz,0 OverTime,0 Overje,0 HourTime,0 Hourje,0 OtherJe,0 je, rqtime,'否' as is_zx "
                +" from [Web_LaborageDetailed] where ygbh not in(select ygbh from [Web_OverTime] where djbh in(select max(djbh) djbh from [Web_LaborageDetailed])) and djbh in(select max(djbh) djbh from [Web_LaborageDetailed] where is_zx='否')";
            returntext = returntext + CalculateExecute(Sql, "【加班记录】基础数据");

            Sql = "insert into [Web_Performance](djbh,ygbh,rqtime,is_zx) select djbh, ygbh, rqtime,'否' as is_zx from [Web_LaborageDetailed] where ygbh not in(select ygbh from [Web_Performance] where djbh in(select max(djbh) djbh from [Web_LaborageDetailed])) and djbh in(select max(djbh) djbh from [Web_LaborageDetailed] where is_zx='否')";
            returntext = returntext + CalculateExecute(Sql, "【提成记录】基础数据");

            Sql = "insert into [Web_Evaluation] (djbh,ygbh,jbgz,rqtime,is_zx) select djbh, ygbh,case when jbgz<1000 then cast(1200.00/30 as decimal(38,2)) else cast(jbgz/30 as decimal(38,2)) end as jbgz,rqtime,'否' as is_zx from [Web_LaborageDetailed] where ygbh not in(select ygbh from [Web_Evaluation] where djbh in(select max(djbh) djbh from [Web_LaborageDetailed])) and djbh in(select max(djbh) djbh from [Web_LaborageDetailed] where is_zx='否')";
            returntext = returntext + CalculateExecute(Sql, "【考勤记录】基础数据");

            Sql = "insert into [Web_SocialSecurity] (djbh,ygbh,rqtime,is_zx) select djbh, ygbh,rqtime,'否' as is_zx from [Web_LaborageDetailed] where ygbh not in(select ygbh from [Web_SocialSecurity] where djbh in(select max(djbh) djbh from [Web_LaborageDetailed])) and djbh in(select max(djbh) djbh from [Web_LaborageDetailed] where is_zx='否')";
            returntext = returntext + CalculateExecute(Sql, "【保险记录】基础数据");

            Sql = "insert into [Web_Violation] (djbh,ygbh,rqtime,is_zx) select djbh, ygbh,rqtime,'否' as is_zx from [Web_LaborageDetailed] where ygbh not in(select ygbh from [Web_Violation] where djbh in(select max(djbh) djbh from [Web_LaborageDetailed])) and djbh in(select max(djbh) djbh from [Web_LaborageDetailed] where is_zx='否')";
            returntext = returntext + CalculateExecute(Sql, "【其它代扣】款项");
            //正在导入模板工资数据
            returntext = returntext + "</textarea><br /><br /><font color=red>正在导入模板工资数据...</font><br /><br />";
            returntext = returntext + "<textarea cols=100 rows=5>";

            Sql = "update b set b.sbcdcs=a.sbcdcs,b.xbztcs=a.xbztcs,b.sbwkqcs=a.sbwkqcs,b.xbwkqcs=a.xbwkqcs,b.nxjts=a.nxjts,b.sjts=a.sjts,b.bjts=a.bjts from [ExcelDao] a,[Web_Evaluation] b where a.ygbh=b.ygbh and a.month=b.rqtime";
            returntext = returntext + CalculateExecute(Sql, "模板数据插入考勤表");

            Sql = "update b set b.OverTime=a.OverTime,b.HourTime=a.HourTime,b.OtherJe=a.Otherje from [ExcelDao] a,[Web_OverTime] b where a.ygbh=b.ygbh and a.month=b.rqtime";
            returntext = returntext + CalculateExecute(Sql, "模板数据插入加班表");

            Sql = "update b set b.qttc=a.yygz from [ExcelDao] a,[Web_Performance] b where a.ygbh=b.ygbh and a.month=b.rqtime";
            returntext = returntext + CalculateExecute(Sql, "模板数据插入提成表");

            Sql = "update b set b.sdje=a.sdf,b.qtdk=a.qtdk from [ExcelDao] a,[Web_LaborageOther] b where a.ygbh=b.ygbh and a.month=b.rqtime";
            returntext = returntext + CalculateExecute(Sql, "模板数据插入其它代扣表");

            Sql = "update b set b.qtje=a.wgwj from [ExcelDao] a,[Web_Violation] b where a.ygbh=b.ygbh and a.month=b.rqtime";
            returntext = returntext + CalculateExecute(Sql, "模板数据插入考勤表");

            Sql = "update b set b.jxf=a.jxf,b.qtgz=a.qtgz,b.sdf=a.sdf,b.qtdk=a.qtdk from [ExcelDao] a,[Web_LaborageDetailed] b where a.ygbh=b.ygbh and a.month=b.rqtime";
            returntext = returntext + CalculateExecute(Sql, "模板数据插入工资主表");

            //正在计算工资数据
            returntext = returntext + "</textarea><br /><br /><font color=red>正在计算工资数据...</font><br /><br />";
            returntext = returntext + "<textarea cols=100 rows=22>";
            Sql = "update [Web_LaborageDetailed] set jxf=0 where ygbh in(select ygbh from [Web_Userinfo] where  SalaryMode in(8)) and is_zx='否'";
            returntext = returntext + CalculateExecute(Sql, "【绩效分】数据");  

            Sql = "update b set b.TaxReductionAmount=a.TaxReductionAmount from [Web_Userinfo] a,[Web_LaborageDetailed] b where a.ygbh=b.ygbh and b.is_zx='否'";
            returntext = returntext + CalculateExecute(Sql, "【应税扣除金额】数据");

            Sql = "update a set a.je=a.pfje+a.fkje+a.qtje from [Web_Violation] a,[Web_LaborageDetailed] b where a.ygbh=b.ygbh and a.djbh=b.djbh and b.is_zx='否'";
            returntext = returntext + CalculateExecute(Sql, "【其它应扣1明细】数据");

            Sql = "update b set b.wgwj=a.je from [Web_Violation] a,[Web_LaborageDetailed] b where a.ygbh=b.ygbh and a.djbh=b.djbh and b.is_zx='否'";
            returntext = returntext + CalculateExecute(Sql, "【其它应扣2明细】数据");

            Sql = "update a set a.je=a.fzje+a.sdje+a.jkje+a.qtdk from [Web_LaborageOther] a,[Web_LaborageDetailed] b where a.ygbh=b.ygbh and a.djbh=b.djbh and b.is_zx='否'";
            returntext = returntext + CalculateExecute(Sql, "【其它代扣1汇总】数据");

            Sql = "update b set b.dkhj=a.je from [Web_LaborageOther] a,[Web_LaborageDetailed] b where a.ygbh=b.ygbh and a.djbh=b.djbh and a.je<>b.dkhj and b.is_zx='否'";
            returntext = returntext + CalculateExecute(Sql, "【其它代扣2汇总】数据");
            
            Sql = "update [Web_Performance] set fhpzsje=fhpzs*" + Laborage[0] + ",fhjsje=fhjs*" + Laborage[1] + ",shpzsje=shpzs*" + Laborage[2] + ",shjsje=shjs*" + Laborage[3] + ",cccsje=cccs*" + Laborage[4] + ",pscsje=pscs*" + Laborage[5] + ",qfhjsje=qfhjs*" + Laborage[6] + ",shhjsje=shhjs*" + Laborage[7] + ",gccsje=gccs*" + Laborage[8] + ",pjsje=pjs*" + Laborage[9] + ",fuhpzsje=fuhpzs*" + Laborage[10] + ",zjsje=zjs*" + Laborage[11] + ",dwsje=dws*" + Laborage[12] + ",lhpzsje=lhpzs*" + Laborage[13] + ",ykjsje=ykjs*" + Laborage[14] + ",byxhjsje=byxhjs*" + Laborage[15] + ",byshjsje=byshjs*" + Laborage[16] + ",je=isnull(fhpzsje,0)+isnull(fhjsje,0)+isnull(shpzsje,0)+isnull(shjsje,0)+isnull(cccsje,0)+isnull(pscsje,0)+isnull(qfhjsje,0)+isnull(shhjsje,0)+isnull(gccsje,0)+isnull(pjsje,0)+isnull(fuhpzsje,0)+isnull(zjsje,0)+isnull(dwsje,0)+isnull(lhpzsje,0)+isnull(ykjsje,0)+isnull(byxhjsje,0)+isnull(byshjsje,0)+isnull(hktcje,0)+isnull(hhtcje,0)-isnull(whkfxje,0)-isnull(whhfxje,0)+isnull(dptc,0)-isnull(zwdk,0)-isnull(tcqtdk,0)+isnull(qttc,0) where is_zx='否'";
            returntext = returntext + CalculateExecute(Sql, "【储运部计件提成工资】数据");

            Sql = "update a set a.pay=b.jxgz,a.hzjxf=projxf,a.pjjxf=b.pjjxf,a.SalaryPay=a.jxf*b.jxgz/100 from [Web_SalaryPerformance] a,(select Sum(jxgz) jxgz,Sum(jxf) projxf,round(convert(dec,Sum(jxf))/Count(*),2) pjjxf,bmid,rqtime From [Web_Userinfo] a,[Web_LaborageDetailed] b where a.ygbh=b.ygbh and  a.SalaryMode=9 and  jxf<>0 and b.is_zx='否' and state<>'离职' and state<>'停薪留职' group by bmid,rqtime) b where a.bmid=b.bmid and a.is_zx='否' and a.rqtime=b.rqtime";
            returntext = returntext + CalculateExecute(Sql, "【各部门】【绩效工资总额】【实得绩效工资总额】【绩效分总额】【绩效平均分】数据");

            Sql = "update b set b.jxxs=case when a.pjjxf=0 then 0 else b.jxf/a.pjjxf end from [Web_SalaryPerformance] a,[Web_LaborageDetailed] b,[Web_UserInfo] c where a.rqtime=b.rqtime and a.is_zx='否' and b.is_zx='否' and a.bmid=c.bmid and b.ygbh=c.ygbh and state<>'离职' and state<>'停薪留职'";
            returntext = returntext + CalculateExecute(Sql, "【绩效系数】数据");

            Sql = "update a set a.jbgz=case when b.jbgz<1000 then cast(1200.00/30 as decimal(38,2)) else cast(b.jbgz/30 as decimal(38,2)) end from [Web_Evaluation] a,[Web_LaborageDetailed] b where a.ygbh=b.ygbh and a.djbh=b.djbh and b.is_zx='否'";
            returntext = returntext + CalculateExecute(Sql, "【考勤考核基础工资】数据");

            Sql = "update a set a.sbwkqje=a.sbwkqcs*" + LaborageAssess[0] + ",a.sbcdje=a.sbcdcs*" + LaborageAssess[1] + ",a.xbwkqje=a.xbwkqcs*" + LaborageAssess[0] + ",a.xbztje=a.xbztcs*" + LaborageAssess[1] + ",a.sjje=a.sjts*a.jbgz,bjje=bjts*" + LaborageAssess[2] + "*a.jbgz,a.kgje=a.kgts*" + LaborageAssess[3] + "*a.jbgz from [Web_Evaluation] a,[Web_LaborageDetailed] b where a.ygbh=b.ygbh and a.djbh=b.djbh and b.is_zx='否'";
            returntext = returntext + CalculateExecute(Sql, "【考勤考核明细】数据");

            Sql = "update a set a.je=a.sbwkqje+a.sbcdje+a.xbwkqje+a.xbztje+a.sjje+a.bjje+a.kgje+a.kqq from [Web_Evaluation] a,[Web_LaborageDetailed] b where a.ygbh=b.ygbh and a.djbh=b.djbh and b.is_zx='否'";
            returntext = returntext + CalculateExecute(Sql, "【考勤考核汇总】数据");

            Sql = "update b set b.kqkh=a.sbwkqje+a.sbcdje+a.xbwkqje+a.xbztje+a.sjje+a.bjje+a.kgje+a.kqq from [Web_Evaluation] a,[Web_LaborageDetailed] b where a.ygbh=b.ygbh and a.djbh=b.djbh and b.is_zx='否'";
            returntext = returntext + CalculateExecute(Sql, "【考勤考核汇总】数据");

            Sql = "update a Set sfjxgz=case when sumjx<>0 then (jxgz*jxxs)/sumjx*SalaryPay else 0 end from [Web_LaborageDetailed] a,[Web_UserInfo] b,(select sum(jxgz*jxxs) as sumjx,b.bmid from [Web_LaborageDetailed] a,[Web_UserInfo] b where a.ygbh=b.ygbh and is_zx='否'and jxf<>0 and salarymode in(8,9) group by bmid) c,[Web_SalaryPerformance] d where a.rqtime=d.rqtime and d.bmid=b.bmid and b.bmid=c.bmid and a.ygbh=b.ygbh and b.SalaryMode=9 and a.is_zx='否' and state<>'离职' and state<>'停薪留职'";
            returntext = returntext + CalculateExecute(Sql, "【普通员工实发绩效】数据");

            Sql = "update a Set sfjxgz=a.jxf*jxgz/100+c.jxf*0.6*jxgz/100 from [Web_LaborageDetailed] a,[Web_UserInfo] b,[Web_SalaryPerformance] c where a.ygbh=b.ygbh and a.rqtime=c.rqtime and b.bmid=c.bmid and b.SalaryMode=10 and a.is_zx='否'";
            returntext = returntext + CalculateExecute(Sql, "【中层实发绩效】数据");

            Sql = "update a Set sfjxgz=jxgz  From [Web_LaborageDetailed] a,[Web_Userinfo] b Where a.ygbh=b.ygbh and  b.SalaryMode in(7,8) and a.is_zx='否'";
            returntext = returntext + CalculateExecute(Sql, "【（年薪、定薪）实发绩效】数据");

            Sql = "update b set gjj=isnull(case when jbgz=0 then 0 else case when state='外派' or state='实习' then 0 when state<>'外派' then case when AccumulationFundMode=46 then " + LaborageAF[0] + " when AccumulationFundMode=47 then " + LaborageAF[1] + " when AccumulationFundMode=48 then " + LaborageAF[2] + " when AccumulationFundMode=49 then " + LaborageAF[3] + " else 0 end end end,0) from [Web_Userinfo] a,[Web_LaborageDetailed] b where a.ygbh=b.ygbh and b.is_zx='否' and a.beactive='是'";
            returntext = returntext + CalculateExecute(Sql, "【公积金】数据");

            Sql = "update b set  glgz=isnull(case when jbgz=0 then 0 else case when state='试用' or state='外派' or state='实习' then 0 when state<>'外派' and SeniorityMode=27 then  datediff(month,rsjbqyshj,rtrim(rqtime)+'-01')/12*" + LaborageSeniority[2] + " when state<>'试用' and state<>'外派' and SeniorityMode=26 then datediff(month,rsjbqyshj,rtrim(rqtime)+'-01')/12*" + LaborageSeniority[1] + " when state<>'试用' and state<>'外派' and SeniorityMode=25 then datediff(month,rsjbqyshj,rtrim(rqtime)+'-01')/12*" + LaborageSeniority[0] + " when state<>'试用' and state<>'外派' and SeniorityMode=0 then datediff(month,rsjbqyshj,rtrim(rqtime)+'-01')/12*" + LaborageSeniority[0] + " end end,0) from [Web_Userinfo] a,[Web_LaborageDetailed] b where a.ygbh=b.ygbh and b.is_zx='否' and a.beactive='是'";
            returntext = returntext + CalculateExecute(Sql, "【工龄工资】数据");

            Sql = "update a set a.jbgz=b.jbgz/26,Overje=OverTime*b.jbgz/26,Hourje=HourTime*12,je=OverTime*b.jbgz/26+HourTime*12+a.OtherJe from [Web_OverTime] a,[Web_LaborageDetailed] b where a.djbh=b.djbh and a.ygbh=b.ygbh and b.is_zx='否';update b set b.jiabgz=OverTime*b.jbgz/26+HourTime*12+a.OtherJe from [Web_OverTime] a,[Web_LaborageDetailed] b where a.djbh=b.djbh and a.ygbh=b.ygbh and b.is_zx='否'";
            returntext = returntext + CalculateExecute(Sql, "【加班工资】数据");
            
            Sql = "update [Web_LaborageDetailed]  Set xdz=(jbgz+gwbt+jxgz+cqgz+jtbt+gwgz)/13.5 where is_zx='否'";
            returntext = returntext + CalculateExecute(Sql, "【薪点值】数据");

            Sql = "update a Set a.yygz=isnull(b.je,0) from [Web_LaborageDetailed] a left join [Web_Performance] b on a.ygbh=b.ygbh and a.djbh=b.djbh where a.is_zx='否'";
            returntext = returntext + CalculateExecute(Sql, "【计件提成工】数据");

            Sql = "update b set yfhj=(case when jbgz >0 then jbgz else 0 end)/dbo.udf_DaysInMonth(rsjbqyshj)*(datediff(day,rsjbqyshj,left(rsjbqyshj,7)+'-'+cast(dbo.udf_DaysInMonth(rsjbqyshj) as varchar(2)))+1)+gwbt+glgz+cqgz+sfjxgz+jiabgz+jtbt+dhf+qtgz+gwgz+yygz,ykhj=kqkh+wgwj+bxf+gjj,ykgz=kqkh+wgwj+bxf+gjj,sfgz=((case when jbgz>0 then jbgz else 0 end)/dbo.udf_DaysInMonth(rsjbqyshj)*(datediff(day,rsjbqyshj,left(rsjbqyshj,7)+'-'+cast(dbo.udf_DaysInMonth(rsjbqyshj) as varchar(2)))+1)+gwbt+glgz+cqgz+sfjxgz+jiabgz+jtbt+dhf+qtgz+yygz+gwgz)-(kqkh+wgwj+bxf+gjj),ffgz=((case when jbgz>0 then jbgz else 0 end)/dbo.udf_DaysInMonth(rsjbqyshj)*(datediff(day,rsjbqyshj,left(rsjbqyshj,7)+'-'+cast(dbo.udf_DaysInMonth(rsjbqyshj) as varchar(2)))+1)+gwbt+glgz+cqgz+sfjxgz+jiabgz+jtbt+dhf+qtgz+yygz+gwgz)-(kqkh+wgwj+bxf+gjj) from [Web_UserInfo] a,[Web_LaborageDetailed] b where a.ygbh=b.ygbh and is_zx='否' and rqtime=left(rsjbqyshj,7)";
            returntext = returntext + CalculateExecute(Sql, "【总工资表】【新员工工资】数据");
            //合并报税
             Sql = "update a set a.hbtaxje=b.sfgz from [Web_LaborageDetailed] a,[Web_LaborageDetailed] b where a.ygbh=b.ygbh and a.is_zx='否' and left(rtrim(a.rqtime),4)=left(rtrim(b.rqtime),4) and right(rtrim(a.rqtime),2)='01' and right(rtrim(b.rqtime),2)='00'";
             returntext = returntext + CalculateExecute(Sql, "【年终奖】【报税】数据");
            /*
             Sql = "update a set "
                 + "taxje="
                 + "case when (b.sfgz-5000*Taxmonth-taxreductionAmount*Taxmonth)*taxRate/100-TaxJehj-TaxDeduction>0 then "
                 + "(b.sfgz-5000*Taxmonth-taxreductionAmount*Taxmonth)*taxRate/100 "
                 + "-TaxJehj-TaxDeduction else 0 end "
                 + "from [Web_LaborageDetailed] a(nolock) "
                 + "inner join (select SUM(sfgz+hbtaxje)as sfgz,SUM(case when is_zx='是' then taxje else 0 end) as taxjehj,count(*) taxmonth,left(rq,4) as taxyear,ygbh from [Web_LaborageDetailed](nolock) where right(rtrim(rqtime),2)!='00' group by left(rq,4),ygbh) b on a.ygbh=b.ygbh and left(a.rq,4)=left(b.taxyear,4) "
                 + "left join Web_TaxBase on (b.sfgz-5000*Taxmonth-taxreductionAmount*Taxmonth)>MinLaborage and (b.sfgz-5000*Taxmonth-taxreductionAmount*Taxmonth)<=MaxLaborage "
                 + " where is_zx='否' and right(rtrim(a.rqtime),2)!=00";
             returntext = returntext + CalculateExecute(Sql, "计算【所得税】数据");
            */
             Sql = "update a set "
                 + "taxje="
                 + "case when (b.sfgz-5000*Taxmonth-b.taxreductionAmount)*taxRate/100-TaxJehj-TaxDeduction>0 then "
                 + "(b.sfgz-5000*Taxmonth-b.taxreductionAmount)*taxRate/100 "
                 + "-TaxJehj-TaxDeduction else 0 end "
                 + "from [Web_LaborageDetailed] a(nolock) "
                 + "inner join (select SUM(sfgz+hbtaxje)as sfgz,sum(TaxReductionAmount) as TaxReductionAmount,SUM(case when is_zx='是' then taxje else 0 end) as taxjehj,count(*) taxmonth,left(rq,4) as taxyear,ygbh "
                 +" from [Web_LaborageDetailed](nolock) where right(rtrim(rqtime),2)!='00' group by left(rq,4),ygbh) b on a.ygbh=b.ygbh and left(a.rq,4)=left(b.taxyear,4) "
                 + "left join [Web_TaxBase] on (b.sfgz-5000*Taxmonth-b.taxreductionAmount)>MinLaborage and (b.sfgz-5000*Taxmonth-b.taxreductionAmount)<=MaxLaborage "
                 + " where is_zx='否' and right(rtrim(a.rqtime),2)!=00";
             returntext = returntext + CalculateExecute(Sql, "【所得税】数据");
             
            Sql = "update d set radix=b.SocialSecurityMode,d.WagePayable=a.yfhj,"
                + " d.cInsurance=case when a.yfhj<c.MinInsurance then c.MinInsurance when a.yfhj>c.MaxInsurance then c.MaxInsurance else a.yfhj end*c.cRadixInsurance,"
                + " d.cMedicare =case when a.yfhj<c.MinMedicare then c.MinMedicare when a.yfhj>c.MaxMedicare then c.MaxMedicare else a.yfhj end*c.cRadixMedicare ,"
                + " d.cIndustrialInjury =case when a.yfhj<c.MinIndustrialInjury then c.MinIndustrialInjury when a.yfhj>c.MaxIndustrialInjury then c.MaxIndustrialInjury else a.yfhj end*c.cRadixIndustrialInjury,"
                + " d.cUnemployed =case when a.yfhj<c.MinUnemployed then c.MinUnemployed when a.yfhj>c.MaxUnemployed then c.MaxUnemployed else a.yfhj end*c.cRadixUnemployed,"
                + " d.cSeriousillness =case when a.yfhj<c.MinSeriousillness then c.MinSeriousillness when a.yfhj>c.MaxSeriousillness then c.MaxSeriousillness else a.yfhj end*c.cRadixSeriousillness,"
                + " d.cMaternity =case when a.yfhj<c.MinMaternity then c.MinMaternity when a.yfhj>c.MaxMaternity then c.MaxMaternity else a.yfhj end*c.cRadixMaternity,"
                + " d.pInsurance =case when b.SocialSecurityMode<c.MinInsurance then c.MinInsurance when b.SocialSecurityMode>c.MaxInsurance then c.MaxInsurance else b.SocialSecurityMode end*c.pRadixInsurance,"
                + " d.pMedicare =case when b.SocialSecurityMode<c.MinMedicare then c.MinMedicare when b.SocialSecurityMode>c.MaxMedicare then c.MaxMedicare else b.SocialSecurityMode end*c.pRadixMedicare,"
                + " d.pIndustrialInjury =case when b.SocialSecurityMode<c.MinIndustrialInjury then c.MinIndustrialInjury when b.SocialSecurityMode>c.MaxIndustrialInjury then c.MaxIndustrialInjury else b.SocialSecurityMode end*c.pRadixIndustrialInjury,"
                + " d.pUnemployed =case when b.SocialSecurityMode<c.MinUnemployed then c.MinUnemployed when b.SocialSecurityMode>c.MaxUnemployed then c.MaxUnemployed else b.SocialSecurityMode end*c.pRadixUnemployed,"
                + " d.pSeriousillness =case when b.SocialSecurityMode<c.MinSeriousillness then c.MinSeriousillness when b.SocialSecurityMode>c.MaxSeriousillness then c.MaxSeriousillness else b.SocialSecurityMode end*c.pRadixSeriousillness,"
                + " d.pMaternity =case when b.SocialSecurityMode<c.MinMaternity then c.MinMaternity when b.SocialSecurityMode>c.MaxMaternity then c.MaxMaternity else b.SocialSecurityMode end*c.pRadixMaternity,"
                + " d.SocialSecurity=c.SetInsurance"
                + " from [Web_LaborageDetailed] a,[Web_UserInfo] b,[Web_SiteSettings] c,[Web_SocialSecurity] d where a.ygbh=b.ygbh and a.djbh=d.djbh and a.ygbh=d.ygbh and a.is_zx='否' and right(rtrim(a.rqtime),2)!=00";
            returntext = returntext + CalculateExecute(Sql, "【社会保险】数据");

            Sql = "update b set sfjbgz=(case when jbgz >0 then jbgz else 0 end)/dbo.udf_DaysInMonth(rsjbqyshj)*(datediff(day,rsjbqyshj,left(rsjbqyshj,7)+'-'+cast(dbo.udf_DaysInMonth(rsjbqyshj) as varchar(2)))+1)"
                +" from [Web_UserInfo] a,[Web_LaborageDetailed] b "
                + "where a.ygbh=b.ygbh and is_zx='否' and rqtime=left(rsjbqyshj,7)";
            returntext = returntext + CalculateExecute(Sql, "【新员工】【实发工资】数据");

            Sql = "update [Web_LaborageDetailed] Set "
                + "yfhj=case when sfjbgz<>0 then sfjbgz else jbgz end+gwbt+glgz+cqgz+sfjxgz+jiabgz+jtbt+dhf+qtgz+gwgz+yygz-wgwj-kqkh,"
                +"ykhj=bxf+gjj+dkhj,ykgz=bxf+gjj,"
                + "sfgz=(case when sfjbgz<>0 then sfjbgz else jbgz end+gwbt+glgz+cqgz+sfjxgz+jiabgz+jtbt+dhf+qtgz+yygz+gwgz)-(kqkh+wgwj+bxf+gjj),"
                + "ffgz=(case when sfjbgz<>0 then sfjbgz else jbgz end+gwbt+glgz+cqgz+sfjxgz+jiabgz+jtbt+dhf+qtgz+yygz+gwgz)-(kqkh+wgwj+bxf+gjj) "
                +"where is_zx='否'";
            returntext = returntext + CalculateExecute(Sql, "【总工资表】数据");
            /*
            Sql = "update b set "
                +"yfhj=(case when jbgz >0 then jbgz else 0 end)/dbo.udf_DaysInMonth(rsjbqyshj)*(datediff(day,rsjbqyshj,left(rsjbqyshj,7)+'-'+cast(dbo.udf_DaysInMonth(rsjbqyshj) as varchar(2)))+1)+gwbt+glgz+cqgz+sfjxgz+jiabgz+jtbt+dhf+qtgz+gwgz+yygz-wgwj-kqkh,"
                +"ykhj=kqkh+wgwj+bxf+gjj,ykgz=kqkh+wgwj+bxf+gjj,"
                +"sfgz=((case when jbgz>0 then jbgz else 0 end)/dbo.udf_DaysInMonth(rsjbqyshj)*(datediff(day,rsjbqyshj,left(rsjbqyshj,7)+'-'+cast(dbo.udf_DaysInMonth(rsjbqyshj) as varchar(2)))+1)+gwbt+glgz+cqgz+sfjxgz+jiabgz+jtbt+dhf+qtgz+yygz+gwgz)-(kqkh+wgwj+bxf+gjj),"
                +"ffgz=((case when jbgz>0 then jbgz else 0 end)/dbo.udf_DaysInMonth(rsjbqyshj)*(datediff(day,rsjbqyshj,left(rsjbqyshj,7)+'-'+cast(dbo.udf_DaysInMonth(rsjbqyshj) as varchar(2)))+1)+gwbt+glgz+cqgz+sfjxgz+jiabgz+jtbt+dhf+qtgz+yygz+gwgz-kqkh-wgwj)-(bxf+gjj)"
                +" from [Web_UserInfo] a,[Web_LaborageDetailed] b "
                +"where a.ygbh=b.ygbh and is_zx='否' and rqtime=left(rsjbqyshj,7)";
            returntext = returntext + CalculateExecute(Sql, "总工资表新员工工资更新b");
            */
            Sql = "update [Web_Laborage] set je=(select sum(yfhj) from [Web_LaborageDetailed] where is_zx='否') where is_zx='否'";
            returntext = returntext + CalculateExecute(Sql, "【工资汇总】数据");
            Sql = "update [Web_LaborageDetailed] Set ysje=case when (sfgz-5000)>0 then sfgz-5000 else 0 end ,sqje=sfgz,shje=case when (sfgz-taxje-dkhj)<> 0 then sfgz-taxje-dkhj else sfgz-dkhj end where is_zx='否'";
            returntext = returntext + CalculateExecute(Sql, "【税后工资】数据");

            Sql = "delete from [ExcelDao]";
            CalculateExecute(Sql, "【临时表】数据");

            returntext = returntext + "</textarea><br><br>";

            return returntext;
        }
        catch (Exception ex)
        {
            WriteLog(ex.ToString(), true);
            return "ERROR :" + ex.Message;
        }
    }
    /// <summary>
    /// 新建工资表
    /// </summary>
    /// <returns></returns>
    public string NewLaborage(string month)
    {
        try
        {           
            PubMAX();
            int maxbh = int.Parse(djbh.Substring(6, 8)) + 1;
            string r = "00000000" + maxbh.ToString();
            string ndjbh = "GZBZDA" + r.Substring(maxbh.ToString().Length, 8);

            string result = "";
            if (shelper.GetString("select top 1 1 from [Web_LaborageDetailed] where rqtime='"+month+"'") == "1")
            {
                result = "已存在" + month + "的工资表";
            }
            else if (shelper.GetString("select top 1 1 from [Web_LaborageDetailed] where is_zx='否'")=="1")
            {
                result = "上月工资没有执行，不能新建" + month + "的工资表";
            } 
            else
            {
            string sql = null;
            sql = "insert into [Web_Laborage](djbh,rq,je,is_zx,yitiqu,dj_zdyxg,rqtime) values ('" + ndjbh + "',convert(varchar(10),getdate(),120),0,'否','否','是','" + month + "')";
            shelper.ExecuteSql(sql);
            sql = "update maxbh set recnum=recnum+1 where biaoshi='GZB'";
            shelper.ExecuteSql(sql);
            sql = "insert into [Web_LaborageDetailed](djbh,ygbh,rq,is_zx,dj_zdyxg,rqtime) select '" + ndjbh + "' djbh,ygbh,convert(varchar(10),getdate(),120) rq,'否' is_zx,'是' dj_zdyxg,'" + month + "' rqtime from [Web_UserInfo] where beactive='是' and state not in('离职','停薪留职') and ygbh not in(select ygbh from [Web_LaborageDetailed] where rqtime ='" + month + "')";
            shelper.ExecuteSql(sql);
             }
            return result;            
        }
        catch (Exception ex)
        {
            WriteLog(ex.ToString(), true);
            return ex.Message;
        }
    }
    /// <summary>
    /// 引用工资表
    /// </summary>
    /// <returns></returns>
    public string QuoteLaborage(string month)
    {
        try
        {
            PubMAX();
            int maxbh = int.Parse(djbh.Substring(6, 8)) + 1;
            string r = "00000000" + maxbh.ToString();
            string ndjbh = "GZBZDA" + r.Substring(maxbh.ToString().Length, 8);
            
            string result = "";
            if (shelper.GetString("select top 1 1 from [Web_LaborageDetailed] where rqtime='" + month + "'") == "1")
            {
                result = "已存在" + month + "的工资表";
            }
            else
            {
                string sql = null;
                sql = "insert into [Web_Laborage](djbh,rq,je,is_zx,yitiqu,dj_zdyxg,rqtime) values ('" + ndjbh + "',convert(varchar(10),getdate(),120),0,'否','否','是','" + month + "')";
                shelper.ExecuteSql(sql);
                sql = "update maxbh set recnum=recnum+1 where biaoshi='GZB'";
                shelper.ExecuteSql(sql);
                sql = "insert into [Web_LaborageDetailed](djbh,ygbh,jbgz,gwbt,glgz,cqgz,jxgz,sfjxgz,jiabgz,dhf,qtgz,yygz,yfhj,kqkh,wgwj,bxf,sdf,pxf,qtdk,ykhj,ykgz,sfgz,beizhu,ygqz,rq,is_zx,dj_zdyxg,rqtime,blgz,shul,ffgz,blgzs,jtbt,jxf,jxxs,xdz,gwgz,gjj) select '" + ndjbh + "' djbh,isnull(b.ygbh,a.ygbh) ygbh,isnull(b.jbgz,0) jbgz,isnull(b.gwbt,0) gwbt,isnull(b.glgz,0) glgz,isnull(b.cqgz,0) cqgz,isnull(b.jxgz,0) jxgz,0 sfjxgz,0 jiabgz,isnull(b.dhf,0) dhf,0 qtgz,0 yygz,0 yfhj,0 kqkh,0 wgwj,isnull(b.bxf,0) bxf,0 sdf,0 as pxf,0 qtdk,0 ykhj,0 ykgz,0 sfgz,'' beizhu,isnull(b.ygqz,'') ygqz,convert(varchar(10),getdate(),120) rq,'否' is_zx,'是' dj_zdyxg,'" + month + "' rqtime,0 blgz,0 shul,0 ffgz,0 blgzs,isnull(b.jtbt,0) jtbt,0 jxf,0 jxxs,0 xdz,isnull(b.gwgz,0) gwgz,isnull(b.gjj,0) gjj from [Web_UserInfo] a left join [Web_LaborageDetailed] b on a.ygbh=b.ygbh and b.djbh='" + djbh + "' where beactive='是' and state<>'离职'";
                shelper.ExecuteSql(sql);
                result = "引用" + month + "工资表成功";
            }
            return result;
        }
        catch (Exception ex)
        {
            WriteLog(ex.ToString(), true);
            return ex.Message;
        }
    }
    /// <summary>
    /// 插入工资表
    /// </summary>
    /// <returns></returns>
    public string InsertLaborage()
    {
        try
        {
            PubMAX();
            string sql = "";
            sql = "insert into [Web_LaborageDetailed](djbh,ygbh,rq,is_zx,dj_zdyxg,rqtime) select '" + djbh + "' djbh,ygbh,convert(varchar(10),getdate(),120) rq,'否' is_zx,'是' dj_zdyxg,'" + month + "' rqtime from [Web_UserInfo] where beactive='是' and state not in('离职','停薪留职') and ygbh not in(select ygbh from [Web_LaborageDetailed] where rqtime ='" + month + "')";
            shelper.ExecuteSql(sql);
            return "插入新员工工资成功";
        }
        catch (Exception ex)
        {
            WriteLog(ex.ToString(), true);
            return "插入新员工工资失败";
        }
    }
    /// <summary>
    /// 删除工资表
    /// </summary>
    /// <param name="djbh">单据编号</param>
    /// <returns></returns>
    public string DeleteLaborage(string djbh)
    {
        string sql = "";
        sql = "";
        shelper.ExecuteSql(sql);
        return "删除工资表成功";
    }

    #region 错误日志
    public void WriteLog(string msg, bool isdebug)
    {
        string text = AppDomain.CurrentDomain.BaseDirectory;
        text = Path.Combine(text, "log");
        if (!Directory.Exists(text))
        {
            Directory.CreateDirectory(text);
        }
        if (isdebug)
        {
            text = Path.Combine(text, "debug");
            if (!Directory.Exists(text))
            {
                Directory.CreateDirectory(text);
            }
        }
        text = Path.Combine(text, DateTime.Now.ToString("yyyyMMdd") + ".txt");
        if (!File.Exists(text))
        {
            File.CreateText(text).Close();
        }
        StreamWriter streamWriter = new StreamWriter(text, true, Encoding.UTF8);
        if (isdebug)
        {
            streamWriter.WriteLine("--------------------------------------------------------------------------------------------------------------------------------");
        }
        streamWriter.WriteLine("{0}  {1}", DateTime.Now.ToString("yyyyMMdd HH:mm:ss"), msg);
        if (isdebug)
        {
            streamWriter.WriteLine("--------------------------------------------------------------------------------------------------------------------------------");
        }
        streamWriter.Flush();
        streamWriter.Close();
    }

    public static void WriteLogs(string msg, bool isdebug)
    {
        string text = AppDomain.CurrentDomain.BaseDirectory;
        text = Path.Combine(text, "log");
        if (!Directory.Exists(text))
        {
            Directory.CreateDirectory(text);
        }
        if (isdebug)
        {
            text = Path.Combine(text, "debug");
            if (!Directory.Exists(text))
            {
                Directory.CreateDirectory(text);
            }
        }
        text = Path.Combine(text, DateTime.Now.ToString("yyyyMMdd") + ".txt");
        if (!File.Exists(text))
        {
            File.CreateText(text).Close();
        }
        StreamWriter streamWriter = new StreamWriter(text, true, Encoding.UTF8);
        if (isdebug)
        {
            streamWriter.WriteLine("--------------------------------------------------------------------------------------------------------------------------------");
        }
        streamWriter.WriteLine("{0}  {1}", DateTime.Now.ToString("yyyyMMdd HH:mm:ss"), msg);
        if (isdebug)
        {
            streamWriter.WriteLine("--------------------------------------------------------------------------------------------------------------------------------");
        }
        streamWriter.Flush();
        streamWriter.Close();
    }

    public void ClearLog()
    {
        string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "log");
        if (Directory.Exists(path))
        {
            DirectoryInfo directoryInfo = new DirectoryInfo(path);
            FileInfo[] files = directoryInfo.GetFiles("*.*", SearchOption.AllDirectories);
            FileInfo[] array = files;
            for (int i = 0; i < array.Length; i++)
            {
                FileInfo fileInfo = array[i];
                if (DateTime.Now.Subtract(fileInfo.CreationTime).Days > 30)
                {
                    File.Delete(fileInfo.FullName);
                }
            }
        }
    }
    #endregion
}